
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/04/2011 15:02:36
-- Generated from EDMX file: D:\@yuxinyang\v4\YiDeSoft\BigTrunk\EzDesk-Dev\Services\Apps\EzDesk.Apps.Data\Attendance\EzDeskAttendance.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [EzDeskAttendance];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_EvaluationModifyHistoryEvaluationChangeItem]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EvaluationChangeItemSet] DROP CONSTRAINT [FK_EvaluationModifyHistoryEvaluationChangeItem];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AttendanceInfoSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AttendanceInfoSet];
GO
IF OBJECT_ID(N'[dbo].[LegalHolidaySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LegalHolidaySet];
GO
IF OBJECT_ID(N'[dbo].[ExtraShiftSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ExtraShiftSet];
GO
IF OBJECT_ID(N'[dbo].[LeaveTypeSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LeaveTypeSet];
GO
IF OBJECT_ID(N'[dbo].[DayAttendanceEvaluationSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DayAttendanceEvaluationSet];
GO
IF OBJECT_ID(N'[dbo].[LeaveInfoSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LeaveInfoSet];
GO
IF OBJECT_ID(N'[dbo].[EvaluationModifyApplicationSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EvaluationModifyApplicationSet];
GO
IF OBJECT_ID(N'[dbo].[EvaluationChangeItemSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EvaluationChangeItemSet];
GO
IF OBJECT_ID(N'[dbo].[EvaluationModifyHistorySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EvaluationModifyHistorySet];
GO
IF OBJECT_ID(N'[dbo].[DayAttendanceRemarkSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DayAttendanceRemarkSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'AttendanceInfoSet'
CREATE TABLE [dbo].[AttendanceInfoSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [Date] datetime  NOT NULL,
    [AttendanceTypeValue] int  NOT NULL,
    [ActionTime] datetime  NOT NULL,
    [Explanation] nvarchar(max)  NULL,
    [IsAbnormalAttendance] bit  NOT NULL
);
GO

-- Creating table 'LegalHolidaySet'
CREATE TABLE [dbo].[LegalHolidaySet] (
    [Id] uniqueidentifier  NOT NULL,
    [HolidayName] nvarchar(max)  NOT NULL,
    [Year] int  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [Remark] nvarchar(max)  NULL,
    [EndDate] datetime  NOT NULL
);
GO

-- Creating table 'ExtraShiftSet'
CREATE TABLE [dbo].[ExtraShiftSet] (
    [Id] uniqueidentifier  NOT NULL,
    [ShiftName] nvarchar(max)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [IsNeedWork] bit  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'LeaveTypeSet'
CREATE TABLE [dbo].[LeaveTypeSet] (
    [Id] uniqueidentifier  NOT NULL,
    [LeaveTypeName] nvarchar(max)  NOT NULL,
    [IsForWork] bit  NOT NULL,
    [OrderValue] int  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'DayAttendanceEvaluationSet'
CREATE TABLE [dbo].[DayAttendanceEvaluationSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [Date] datetime  NOT NULL,
    [IsNeedToWork] bit  NOT NULL,
    [LateMiniutes] int  NOT NULL,
    [EarlyMiniutes] int  NOT NULL,
    [IsAbsense] bit  NOT NULL,
    [IsExtraWork] bit  NOT NULL,
    [Manhours] int  NOT NULL,
    [UpdateOn] datetime  NULL
);
GO

-- Creating table 'LeaveInfoSet'
CREATE TABLE [dbo].[LeaveInfoSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [StartTime] datetime  NOT NULL,
    [EndTime] datetime  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [IsAudited] bit  NOT NULL,
    [AuditUserId] uniqueidentifier  NULL,
    [AuditNote] nvarchar(max)  NULL,
    [AuditTime] datetime  NULL,
    [IsAgreed] bit  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [LeaveTypeId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'EvaluationModifyApplicationSet'
CREATE TABLE [dbo].[EvaluationModifyApplicationSet] (
    [Id] uniqueidentifier  NOT NULL,
    [ApplyUserId] uniqueidentifier  NOT NULL,
    [ApplyOn] datetime  NOT NULL,
    [ApplyText] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [StateValue] int  NOT NULL
);
GO

-- Creating table 'EvaluationChangeItemSet'
CREATE TABLE [dbo].[EvaluationChangeItemSet] (
    [Id] uniqueidentifier  NOT NULL,
    [ItemName] nvarchar(max)  NOT NULL,
    [NewValue] nvarchar(max)  NOT NULL,
    [OldValue] nvarchar(max)  NOT NULL,
    [HistoryId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'EvaluationModifyHistorySet'
CREATE TABLE [dbo].[EvaluationModifyHistorySet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [OperatorId] uniqueidentifier  NOT NULL,
    [Date] datetime  NOT NULL,
    [ModifyOn] datetime  NOT NULL
);
GO

-- Creating table 'DayAttendanceRemarkSet'
CREATE TABLE [dbo].[DayAttendanceRemarkSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [Date] datetime  NOT NULL,
    [Remark] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'AttendanceInfoSet'
ALTER TABLE [dbo].[AttendanceInfoSet]
ADD CONSTRAINT [PK_AttendanceInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LegalHolidaySet'
ALTER TABLE [dbo].[LegalHolidaySet]
ADD CONSTRAINT [PK_LegalHolidaySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ExtraShiftSet'
ALTER TABLE [dbo].[ExtraShiftSet]
ADD CONSTRAINT [PK_ExtraShiftSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LeaveTypeSet'
ALTER TABLE [dbo].[LeaveTypeSet]
ADD CONSTRAINT [PK_LeaveTypeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DayAttendanceEvaluationSet'
ALTER TABLE [dbo].[DayAttendanceEvaluationSet]
ADD CONSTRAINT [PK_DayAttendanceEvaluationSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LeaveInfoSet'
ALTER TABLE [dbo].[LeaveInfoSet]
ADD CONSTRAINT [PK_LeaveInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EvaluationModifyApplicationSet'
ALTER TABLE [dbo].[EvaluationModifyApplicationSet]
ADD CONSTRAINT [PK_EvaluationModifyApplicationSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EvaluationChangeItemSet'
ALTER TABLE [dbo].[EvaluationChangeItemSet]
ADD CONSTRAINT [PK_EvaluationChangeItemSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EvaluationModifyHistorySet'
ALTER TABLE [dbo].[EvaluationModifyHistorySet]
ADD CONSTRAINT [PK_EvaluationModifyHistorySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DayAttendanceRemarkSet'
ALTER TABLE [dbo].[DayAttendanceRemarkSet]
ADD CONSTRAINT [PK_DayAttendanceRemarkSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [HistoryId] in table 'EvaluationChangeItemSet'
ALTER TABLE [dbo].[EvaluationChangeItemSet]
ADD CONSTRAINT [FK_EvaluationModifyHistoryEvaluationChangeItem]
    FOREIGN KEY ([HistoryId])
    REFERENCES [dbo].[EvaluationModifyHistorySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EvaluationModifyHistoryEvaluationChangeItem'
CREATE INDEX [IX_FK_EvaluationModifyHistoryEvaluationChangeItem]
ON [dbo].[EvaluationChangeItemSet]
    ([HistoryId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------